

/**
	Author		: Rohan
	Date		: May 25th 2004
	Description	: Search the COOL and CCOL tables and returns the Line Item ID pertaining to the 
			  given sales order ID and Line Item Number. Returns -1 if the given line item 
		          is not existing. Searchged
	Usage		: 
			  DECLARE @LINE_ITEM_ID INT
			  EXEC sp_ct_sls_get_line_item_id 1886, 1, @LINE_ITEM_ID OUT
			  PRINT CONVERT( CHAR, @LINE_ITEM_ID ) 
*/
ALTER PROC sp_ct_sls_get_line_item_id

@SALES_ORDER_ID INT,
@LINE_ITEM_NO   INT,
@LINE_ITEM_ID   INT OUT

AS

BEGIN

SET @LINE_ITEM_ID = -1

SELECT @LINE_ITEM_ID = LINEITEM_ID FROM CUSTOMER_OPEN_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID AND COOL_LINE_ITEM_NO = @LINE_ITEM_NO
IF( @@ROWCOUNT = 0 )
BEGIN
	--IF THE LINE ITEM IS NOT EXISTING IN THE COOL TABLE THEN SEARCHING THE CCOL TABLE 
	SELECT @LINE_ITEM_ID = LINEITEM_ID FROM CUSTOMER_CANCEL_ORDER_LINEITEM WHERE SALESORDER_ID = @SALES_ORDER_ID AND CCOL_LINE_ITEM_NO = @LINE_ITEM_NO
END
END --END OF PROC